The present invention relates to pre-generating structured query language (SQL), and more specifically, to generating static SQL queries from application programming interface (API) defined query systems.
SQL is a standardized query language for requesting information from a database. SQL queries containing SQL statements are embedded into the source code of an application program or they are externalized (e.g., located in a file). Static SQL queries used by an application program are defined in a database before the application program executes them. The table and column names are known prior to compiling the application, with the only unknowns at compile time being the specific data value(s) that the SQL query is searching for, or updating. These unknown values are typically represented as variables. When static SQL is used, the SQL query is pre-compiled, bound, and then compiled into an execution plan as machine executable code prior to being executed in the application program. Static SQL provides a relatively high level of database security because accesses to the database are known in advance of application programs executing queries against the database. In addition, knowing the SQL queries in advance allows database administrators (DBAs) to tune the database (e.g., add an index) or to suggest changes to the SQL statements in a SQL query that may result in better performance of the SQL query during program execution.
Dynamic SQL queries are those that an application program builds and executes at run time. An interactive application program that prompts an end user for key parts of a SQL statement, such as the names of the tables and columns to be searched, is an example of an application program that uses dynamic SQL. The application program builds the SQL queries while it is running, and then submits the queries for processing. Thus, in dynamic SQL, the table and columns names are often not known prior to executing the application program. Dynamic SQL queries are also generated by query systems where a developer uses an API to define a query in an application program instead of an SQL string. A Criteria Query, as described in the Java persistence architecture (JPA) 2.0 specification, is an example of such a query methodology in a object relational mapping (ORM) system. ORM is a widely used technique to bridge the mismatch between a relational model that is based on a tabular database and an object oriented (OO) model which includes objects that are related by properties such as polymorphism and inheritance. ORM libraries automate the conversion of data between these two formats in both directions without the need for an application developer to write conversion code. ORM systems perform this conversion using metadata that describes the mapping between objects and the database.